//! @Author: DengLibin
//! @Date: Create in 2024-11-05 09:46:29
//! @Description: Postgresql

use chrono::{DateTime, NaiveDateTime, Utc};
use my_trait::{MustHaveFieldType, MustHaveTableName, Struct2Map};
use serde::Serialize;
use serde_json::Value;
use sqlx::{
    postgres::{PgPoolOptions, PgRow},
    FromRow, Pool, Postgres, Transaction,
};

use crate::sys::global::{to_global_result, GlobalResult};

use super::super::PageData;

//数据库sechema
pub const SECHEMA: &str = "yiscn_file_sys";

/// @Author: DengLibin
/// @Date: Create in 2024-07-16 14:01:51
/// @Description: 获取连接池
/// @param: url 连接地址 "postgres://postgres:password@localhost/test"
pub async fn get_pg_conn_pool(url: &str, max_connection: u32) -> GlobalResult<Pool<Postgres>> {
    let r = PgPoolOptions::new()
        .max_connections(max_connection)
        .connect(url)
        .await;
    to_global_result(r)
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-17 15:16:16
/// @Description: 获取查询的总数
pub async fn count_total(sql: &str, pool: &Pool<Postgres>) -> GlobalResult<i64> {
    let r = sqlx::query_scalar(format!("SELECT COUNT(1) FROM ({})", sql).as_str())
        .fetch_one(pool)
        .await;

    to_global_result(r)
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-18 16:05:59
/// @Description: 根据id查询
pub async fn get_by_id<E>(id: i64, pool: &Pool<Postgres>) -> GlobalResult<Option<E>>
where
    E: Send + Unpin + for<'r> FromRow<'r, PgRow> + MustHaveTableName,
{
    let sql = format!("SELECT * FROM {}.{} where id=$1", SECHEMA, E::table_name());
    let x = sqlx::query_as::<Postgres, E>(sql.as_str())
        .bind(id)
        .fetch_optional(pool)
        .await;
    to_global_result(x)
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-17 15:31:26
/// @Description: 查询分页数据
pub async fn query_page<E>(page: i32, size: i32, pool: &Pool<Postgres>) -> GlobalResult<PageData<E>>
where
    E: Serialize + Send + Unpin + for<'r> FromRow<'r, PgRow> + MustHaveTableName,
{
    let offset = (page - 1) * size;

    let sql = format!("SELECT * FROM {}.{}", SECHEMA, E::table_name());

    let page_sql = format!("{} limit $1 offset $2", sql);
    let e_future = sqlx::query_as::<Postgres, E>(page_sql.as_str())
        .bind(size)
        .bind(offset)
        .fetch_all(pool);

    let total_future = count_total(sql.as_str(), pool);

    // 使用 tokio::join! 宏并行执行两个异步任务
    let (users, total) = tokio::join!(e_future, total_future);

    let users = to_global_result(users)?;
    let total = to_global_result(total)?;
    Ok(PageData {
        page,
        size,
        total,
        data: users,
    })
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-18 16:42:06
/// @Description: 根据id删除
pub async fn delete_by_id<E>(id: i64, pool: &Pool<Postgres>) -> GlobalResult<()>
where
    E: MustHaveTableName,
{
    let sql = format!("DELETE FROM {}.{} WHERE id=$1", SECHEMA, E::table_name());
    let result = sqlx::query(&sql).bind(id).execute(pool).await;
    to_global_result(result)?;
    Ok(())
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-19 14:46:11
/// @Description: 根据id更新
pub async fn update_one<E>(id: Value, e: E, pool: &Pool<Postgres>) -> GlobalResult<()>
where
    E: MustHaveTableName + MustHaveFieldType + Struct2Map,
{
    // 构建 SQL 查询
    let mut sql = format!("UPDATE {}.{} SET ", SECHEMA, E::table_name());
    let mut index = 0;
    let mut params: Vec<Value> = vec![]; //字段的值
    let mut types: Vec<&str> = vec![]; //字段的类型

    let field_types = E::field_types_map(); //获取字段类型

    let field_values = e.to_map();
    //要更新的字段
    for (field, value) in field_values {
        if field == "id" {
            continue;
        }
        if index > 0 {
            sql.push_str(", ");
        }
        sql.push_str(format!("{} = ${}", field, index + 1).as_str());
        params.push(value);
        let field_type = field_types.get(field.as_str());
        if let Some(type_str) = field_type {
            types.push(type_str); //该字段类型
        } else {
            types.push("");
        }

        index += 1;
    }

    sql.push_str(format!(" WHERE id = ${}", index + 1).as_str());
    //id条件
    params.push(id);
    let id_type_o = field_types.get("id");
    if let Some(id_type) = id_type_o {
        types.push(id_type.as_str()); //id字段类型
    } else {
        return Err(anyhow::anyhow!("id字段类型不存在".to_string()));
    }

    // 开启事务

    let mut tx: Transaction<'_, Postgres> = to_global_result(pool.begin().await)?;
    // 执行 SQL 查询
    // 构建查询并绑定参数id
    let mut query: sqlx::query::Query<Postgres, sqlx::postgres::PgArguments> =
        sqlx::query::<Postgres>(sql.as_str());

    //绑定更新参数
    query = bind_value(params, types, query)?;

    let r = query.execute(&mut *tx).await;
    if let Err(err) = r {
        // 回滚事务
        to_global_result(tx.rollback().await)?;

        return Err(anyhow::anyhow!(err.to_string()));
    } else {
        //提交
        to_global_result(tx.commit().await)?;
    }

    Ok(())
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-19 16:08:51
/// @Description: 添加一个
pub async fn insert_one<E>(e: E, pool: &Pool<Postgres>) -> GlobalResult<E>
where
    E: MustHaveTableName + MustHaveFieldType + Struct2Map,
{
    // 构建 SQL 查询
    let mut sql = format!("INSERT INTO {}.\"{}\"(", SECHEMA, E::table_name());
    let mut index = 0;
    let mut params: Vec<Value> = vec![]; //字段的值
    let mut types: Vec<&str> = vec![]; //字段的类型

    let field_types = E::field_types_map(); //获取字段类型

    let field_values = e.to_map();
    //要添加的字段
    for (field, value) in field_values {
        if index > 0 {
            sql.push_str(", ");
        }
        sql.push_str(format!("{}", field).as_str());
        params.push(value);
        let field_type = field_types.get(field.as_str());
        if let Some(type_str) = field_type {
            types.push(type_str); //该字段类型
        } else {
            types.push("");
        }

        index += 1;
    }

    sql.push_str(")VALUES(");
    for i in 0..params.len() {
        if i > 0 {
            sql.push_str(",");
        }
        sql.push_str(format!("${}", i + 1).as_str());
    }
    sql.push(')');

    log::info!("sql: {} \nparams: {:?}", sql, params);
    // 开启事务

    let mut tx: Transaction<'_, Postgres> = to_global_result(pool.begin().await)?;
    // 执行 SQL 查询
    // 构建查询并绑定参数id
    let mut query: sqlx::query::Query<Postgres, sqlx::postgres::PgArguments> =
        sqlx::query::<Postgres>(sql.as_str());

    //绑定更新参数
    query = bind_value(params, types, query)?;

    let r = query.execute(&mut *tx).await;
    if let Err(err) = r {
        // 回滚事务
        to_global_result(tx.rollback().await)?;

        return Err(anyhow::anyhow!(err.to_string()));
    } else {
        //提交
        to_global_result(tx.commit().await)?;
    }

    Ok(e)
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-19 14:40:00
/// @Description: 绑定参数
fn bind_value<'q>(
    params: Vec<Value>, //参数
    types: Vec<&str>,   //参数类型(和参数一一对应)
    mut query: sqlx::query::Query<'q, Postgres, sqlx::postgres::PgArguments>,
) -> GlobalResult<sqlx::query::Query<'q, Postgres, sqlx::postgres::PgArguments>> {
    let mut index = 0;
    //绑定值
    for value in params {
        let type_s = types[index];
        match value {
            Value::Null => match type_s {
                "Option<DateTime<Utc>>" => query = query.bind::<Option<DateTime<Utc>>>(None),
                "Option<NaiveDateTime>" => query = query.bind::<Option<DateTime<Utc>>>(None),
                "Option<i64>" => query = query.bind::<Option<i64>>(None),
                "Option<i32>" => query = query.bind::<Option<i32>>(None),
                "Option<f64>" => query = query.bind::<Option<f64>>(None),
                "Option<f32>" => query = query.bind::<Option<f32>>(None),
                "Option<String>" => query = query.bind::<Option<String>>(None),

                _ => {
                    return Err(anyhow::anyhow!(format!("Null类型错误:{}", type_s)));
                }
            },
            Value::Bool(b) => query = query.bind::<bool>(b),
            Value::Number(n) => match type_s {
                "Option<i64>" => query = query.bind::<Option<i64>>(Some(n.as_i64().unwrap())),
                "Option<i32>" => {
                    query = query.bind::<Option<i32>>(Some(n.as_i64().unwrap() as i32))
                }
                "Option<f64>" => query = query.bind::<Option<f64>>(Some(n.as_f64().unwrap())),
                "Option<f32>" => {
                    query = query.bind::<Option<f32>>(Some(n.as_f64().unwrap() as f32))
                }
                "i64" => query = query.bind::<i64>(n.as_i64().unwrap()),
                "i32" => query = query.bind::<i32>(n.as_i64().unwrap() as i32),
                "f64" => query = query.bind::<f64>(n.as_f64().unwrap()),
                "f32" => query = query.bind::<f32>(n.as_i64().unwrap() as f32),
                _ => {
                    return Err(anyhow::anyhow!(format!("Number类型错误:{}", type_s)));
                }
            },
            Value::String(s) => match type_s {
                "String" => query = query.bind::<String>(s),
                "Option<String>" => query = query.bind::<Option<String>>(Some(s)),
                "DateTime<Utc>" => {
                    let date_r = s.parse::<DateTime<Utc>>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<DateTime<Utc>>(date);
                }
                "Option<DateTime<Utc>>" => {
                    let date_r = s.parse::<DateTime<Utc>>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<Option<DateTime<Utc>>>(Some(date));
                }
                "NaiveDateTime" => {
                    let date_r = s.parse::<NaiveDateTime>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<NaiveDateTime>(date);
                }
                "Option<NaiveDateTime>" => {
                    let date_r = s.parse::<NaiveDateTime>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<Option<NaiveDateTime>>(Some(date));
                }

                _ => {
                    return Err(anyhow::anyhow!(format!("String类型错误:{}", type_s)));
                }
            },
            _ => {}
        }
        index += 1;
    }
    Ok(query)
}
